// okienko listy obecnosci

unit Unit9;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, db, FileUtil, LR_Class, LR_DBSet, LR_RRect, LR_Shape,
  LR_Desgn, LR_View, LR_BarC, LR_E_HTM, Forms, Controls, Graphics, Dialogs,
  DBGrids, StdCtrls, ExtCtrls, EditBtn, IBQuery, IBDatabase, ZDataset;

type

  { TForm9 }

  TForm9 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    Button5: TButton;
    Button6: TButton;
    Button7: TButton;
    Button8: TButton;
    CheckBox1: TCheckBox;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    Datasource1: TDatasource;
    DateEdit1: TDateEdit;
    DateEdit2: TDateEdit;
    DBGrid1: TDBGrid;
    frBarCodeObject1: TfrBarCodeObject;
    frCompositeReport1: TfrCompositeReport;
    frDBDataSet1: TfrDBDataSet;
    frDesigner1: TfrDesigner;
    frHTMExport1: TfrHTMExport;
    frReport1: TfrReport;
    frRoundRectObject1: TfrRoundRectObject;
    frShapeObject1: TfrShapeObject;
    GroupBox1: TGroupBox;
    ibq1: TIBQuery;
    ibq2: TIBQuery;
    ibt1: TIBTransaction;
    ibt2: TIBTransaction;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Panel1: TPanel;
    Panel2: TPanel;
    Panel3: TPanel;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure Button6Click(Sender: TObject);
    procedure Button7Click(Sender: TObject);
    procedure Button8Click(Sender: TObject);
    procedure CheckBox1Change(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
//    function  GetIdx(txt: string): integer;
    function  FormTime(txt :string):string;
    procedure FillComboBox();
    procedure FillComboBoxFilters();
  end;

var
  Form9: TForm9;

implementation

{$R *.lfm}

{ TForm9 }
uses
  unit15, unit5, unit19;

procedure TForm9.Button5Click(Sender: TObject);
begin
  if ibt2.Active then ibt2.Rollback;
  close;
end;

// podglad raportu
procedure TForm9.Button6Click(Sender: TObject);
begin
  if fileexists('.\obecnosci.lrf') then frReport1.LoadFromFile('.\obecnosci.lrf');
  frReport1.ShowReport;
end;

// czyszczenie przedzialu dat
procedure TForm9.Button7Click(Sender: TObject);
begin
  dateedit1.Clear;
  dateedit2.Clear;
end;

// projektowanie raportu
procedure TForm9.Button8Click(Sender: TObject);
begin
  if fileexists('.\obecnosci.lrf') then frReport1.LoadFromFile('.\obecnosci.lrf');
  frReport1.DesignReport;
end;

procedure TForm9.CheckBox1Change(Sender: TObject);
begin
  if checkbox1.Checked then
  begin
    dateedit1.Enabled:=false;
    dateedit2.Enabled:=false;
    ComboBox1.Enabled:=false;
    ComboBox2.Enabled:=false;
    label1.Enabled:=false;
    label2.Enabled:=false;
    label3.Enabled:=false;
    label4.Enabled:=false;
//    Button2.Enabled:=false;
  end
  else
  begin
    dateedit1.Enabled:=true;
    dateedit2.Enabled:=true;
    ComboBox1.Enabled:=true;
    ComboBox2.Enabled:=true;
    label1.Enabled:=true;
    label2.Enabled:=true;
    label3.Enabled:=true;
    label4.Enabled:=true;
//    Button2.Enabled:=true;
  end;

end;

procedure TForm9.FormShow(Sender: TObject);
var
  flt_a,flt_b : string;
  i           : integer;
begin
  flt_a:='';
  flt_b:='';

  if ibt1.Active then ibt1.Commit;
  ibq1.SQL.Clear;
  ibq1.SQL.Add('select ob.data_start, ob.data_stop, ob.czas_pracy, pr.nazwadl "Pracownik", gr.nazwadl "Grupa", ob.opis,');
  ibq1.SQL.Add('       ob.obecnosci_idx, pr.pracownik_idx');
  ibq1.SQL.Add('from obecnosci ob');
  ibq1.SQL.Add('inner join pracownik pr on pr.pracownik_idx = ob.pracownik_idx');
  ibq1.SQL.Add('inner join prac_grup gr on pr.prac_grup_idx = gr.prac_grup_idx');

  if ComboBox1.Text <> '--wszystko--' then flt_a:='pr.pracownik_idx = '+IntToStr(GetIdx(ComboBox1.Text));
  if ComboBox2.Text <> '--wszystko--' then flt_b:='gr.prac_grup_idx = '+IntToStr(GetIdx(ComboBox2.Text));

  if checkbox1.Checked then ibq1.SQL.Add('where data_stop is null')
  else
  begin
    if (flt_a <> '') or (flt_b <> '') or
       (dateedit1.Date <> 0) or (dateedit2.date <> 0)
    then
      ibq1.SQL.Add('where data_start is not null');

    if flt_a <> '' then ibq1.SQL.Add('and '+flt_a);
    if flt_b <> '' then ibq1.SQL.Add('and '+flt_b);
    if dateedit1.Date <> 0 then
    begin
      ibq1.SQL.Add('and data_start >= :dta');
      ibq1.ParamByName('dta').AsDateTime:=ComposeDateTime(dateedit1.Date,StrToTime('00:00:00'));
    end;

    if dateedit2.Date <> 0 then
    begin
      ibq1.SQL.Add('and data_start <= :dtb');
      ibq1.ParamByName('dtb').AsDateTime:=ComposeDateTime(dateedit2.Date,StrToTime('23:59:59'));
    end;

  end;
  ibq1.SQL.Add('order by data_start');
  try
    ibq1.Transaction.StartTransaction;
    ibq1.Open;

    for i:=0 to DBGrid1.Columns.Count-1 do
    begin
      if pos('_IDX',UpperCase(DBGrid1.Columns[i].FieldName)) <> 0 then DBGrid1.Columns[i].Visible:=false;
    end;

  except
    ibq1.Transaction.Rollback;
  end;
end;

{function TForm9.GetIdx(txt: string): integer;
var
  buf : string;
begin
  buf:='';
  while (txt <> '') and (txt[length(txt)] <> '(') do
  begin
    delete(txt,length(txt),1);
    while (txt <> '') and (txt[length(txt)] in ['0'..'9']) do
    begin
      buf:=txt[length(txt)]+buf;
      delete(txt,length(txt),1);
    end;
  end;
  if buf = '' then GetIdx:=0 else GetIdx:=StrToInt(buf);
end;}

function TForm9.FormTime(txt: string): string;
var
  g,m,s : string;
begin
  g:=''; m:=''; s:='';

  g:=trim(copy(txt,1,pos(':',txt)-1));
  if txt <> '' then
  begin
    delete(txt,1,pos(':',txt));
    m:=trim(copy(txt,1,pos(':',txt)-1));
    if txt <> '' then
    begin
       delete(txt,1,pos(':',txt));
       s:=trim(txt);
    end;
  end;

  while length(g) < 2 do g:='0'+g;
  while length(m) < 2 do m:='0'+m;
  while length(s) < 2 do s:='0'+s;
  FormTime:=g+':'+m+':'+s;
end;

procedure TForm9.FillComboBox;
begin
  if ibt2.Active then ibt2.Rollback;
  ibq2.SQL.Clear;
  ibq2.SQL.Add('select pr.nazwadl,pr.pracownik_idx from pracownik pr order by upper(nazwadl)');
  try
    ibt2.StartTransaction;
    ibq2.Open;

    form15.ComboBox1.Clear;
    ibq2.First;
    while not ibq2.EOF do
    begin
      form15.ComboBox1.Items.Add(ibq2.FieldByName('nazwadl').AsString+' ('+IntToStr(ibq2.FieldByName('pracownik_idx').AsInteger)+')');
      ibq2.Next;
    end;
    ibt2.Commit;
  except
    ibt2.Rollback;
  end;

end;

procedure TForm9.FillComboBoxFilters;
begin
  ComboBox1.Clear;
  ComboBox1.Items.Add('--wszystko--');
  ComboBox1.Text:='--wszystko--';

  ComboBox2.Clear;
  ComboBox2.Items.Add('--wszystko--');
  ComboBox2.Text:='--wszystko--';

  // wypelnienie ComboBox pracownikow
  if ibt2.Active then ibt2.Rollback;

  ibq2.SQL.Clear;
  ibq2.SQL.Add('select nazwadl,pracownik_idx from pracownik order by upper(nazwadl)');
  try
    ibq2.Transaction.StartTransaction;
    ibq2.Open;
    ibq2.First;
    while not ibq2.EOF do
    begin
      ComboBox1.Items.Add(ibq2.FieldByName('nazwadl').AsString+' ('+ IntToStr(ibq2.FieldByName('pracownik_idx').AsInteger) +')');
      ibq2.Next;
    end;

    ibq2.Transaction.Commit;
  except
    ibq2.Transaction.Rollback;
  end;
  // wypelnienie ComboBox grup
  if ibt2.Active then ibt2.Rollback;

  ibq2.SQL.Clear;
  ibq2.SQL.Add('select nazwadl,prac_grup_idx from prac_grup order by upper(nazwadl)');
  try
    ibq2.Transaction.StartTransaction;
    ibq2.Open;
    ibq2.First;
    while not ibq2.EOF do
    begin
      ComboBox2.Items.Add(ibq2.FieldByName('nazwadl').AsString+' ('+ IntToStr(ibq2.FieldByName('prac_grup_idx').AsInteger) +')');
      ibq2.Next;
    end;
    ibq2.Transaction.Commit;
  except
    ibq2.Transaction.Rollback;
  end;
end;

// nowy
procedure TForm9.Button1Click(Sender: TObject);
var
  dta,dtb : TDateTime;
  ean     : string;
  prac    : integer;
begin
  form15.Caption:='Nowa obecnosc';
  if form15.ibq.Active then form15.ibq.Close;
  FillComboBox();
  form15.ibq.Database    :=ibq1.Database;

  if form15.ShowModal = mrOK then
  begin
    dta:=ComposeDateTime(form15.DateEdit1.Date, StrToTime(FormTime(form15.MaskEdit1.Text)));
    if form15.DateEdit2.Date <> 0 then dtb:=ComposeDateTime(form15.DateEdit2.Date, StrToTime(FormTime(form15.MaskEdit2.Text)));
    prac:=GetIdx(form15.ComboBox1.Text);

    if ibt2.Active then ibt2.Rollback;
    ibq2.SQL.Clear;
    ibq2.SQL.Add('select * from pracownik where pracownik_idx = '+IntToStr(prac));
    try
      ibt2.StartTransaction;
      ibq2.Open;
      ean:=ibq2.FieldByName('ean').AsString;
      ibt2.Commit;
    except
      ibt2.Rollback;
    end;

    if ibt2.Active then ibt2.Rollback;
    ibq2.SQL.Clear;
    ibq2.SQL.Add('insert into obecnosci (data_start, data_stop, czas_pracy, ean, opis, pracownik_idx)');
    ibq2.SQL.Add('               values (:dta, :dtb, :czp, :ean, :opis, :prac)');
    ibq2.ParamByName('dta').AsDateTime:=dta;
    if form15.DateEdit2.Date <> 0 then
    begin
      ibq2.ParamByName('dtb').AsDateTime:=dtb;
      ibq2.ParamByName('czp').AsDateTime:=dtb-dta;
    end
    else begin end; // jezeli brak drugiej daty to wstawiane jest null, nie podanie wartosci dla parametru powoduje wstawienie null
    ibq2.ParamByName('ean').AsString:=ean;
    ibq2.ParamByName('opis').AsString:=form15.Edit1.Text;
    ibq2.ParamByName('prac').AsInteger:=prac;
    try
      ibq1.Transaction.StartTransaction;
      ibq1.ExecSQL;
      ibq1.Transaction.Commit;
    except
      ibq1.Transaction.Rollback;
    end;
    FormShow(nil);
  end;
end;

procedure TForm9.Button2Click(Sender: TObject);
begin
  FormShow(nil);
end;

// edycja
procedure TForm9.Button3Click(Sender: TObject);
var
  key_ob  : integer;
  key_pr  : integer;
  buf,a,b : string;
  dta,dtb : TDateTime;
begin
  form15.Caption:='Edycja obecnosci';

  key_ob:=ibq1.FieldByName('obecnosci_idx').AsInteger;
  key_pr:=ibq1.FieldByName('pracownik_idx').AsInteger;

  // pobranie pracownika
  FillComboBox();
  if ibq2.Active then ibq2.Close;
  ibq2.SQL.Clear;
  ibq2.SQL.Add('select * from pracownik where pracownik_idx = '+IntToStr(key_pr));
  try
    ibq2.Transaction.StartTransaction;
    ibq2.Open;
    form15.ComboBox1.Text:=ibq2.FieldByName('nazwadl').AsString+' ('+IntToStr(ibq2.FieldByName('pracownik_idx').AsInteger)+')';
    ibq2.Transaction.Commit;
  except
    ibq2.Transaction.Rollback;
  end;

  if ibq1.FieldByName('data_start').AsDateTime <> 0 then
  begin
    buf:=DateTimeToStr(ibq1.FieldByName('data_start').AsDateTime);
    a:=Copy(buf,1,pos(' ',buf)-1);
    b:=Copy(buf,pos(' ',buf)+1,length(buf));
    form15.DateEdit1.Date:=StrToDate(a);
    form15.MaskEdit1.Text:=b;
  end
  else
    begin
      form15.DateEdit1.Clear;
      form15.MaskEdit1.Clear;
    end;

  if ibq1.FieldByName('data_stop').AsDateTime <> 0 then
  begin
    buf:=DateTimeToStr(ibq1.FieldByName('data_stop').AsDateTime);
    a:=Copy(buf,1,pos(' ',buf)-1);
    b:=Copy(buf,pos(' ',buf)+1,length(buf));
    form15.DateEdit2.Date:=StrToDate(a);
    form15.MaskEdit2.Text:=b;
  end
  else
    begin
      form15.DateEdit2.Clear;
      form15.MaskEdit2.Clear;
    end;

  form15.Edit1.Text:=ibq1.FieldByName('opis').AsString;

  if form15.ShowModal = mrOk then
  begin
    dta:=ComposeDateTime(form15.DateEdit1.Date, StrToTime(FormTime(form15.MaskEdit1.Text)));
    dtb:=ComposeDateTime(form15.DateEdit2.Date, StrToTime(FormTime(form15.MaskEdit2.Text)));
    if ibt2.Active then ibt2.Rollback;
    ibq2.SQL.Clear;
    ibq2.SQL.Add('update obecnosci set data_start = :dta, data_stop = :dtb, ');
    ibq2.SQL.Add('                     czas_pracy = :czp, pracownik_idx = :prac, opis = :opis');
    ibq2.SQL.Add('where obecnosci_idx = :key_ob');
    if dta <> 0 then ibq2.ParamByName('dta').AsDateTime:=dta else ibq2.ParamByName('dta').Value:=null;
    if dtb <> 0 then ibq2.ParamByName('dtb').AsDateTime:=dtb else ibq2.ParamByName('dtb').Value:=null;
    if (dta <> 0) and (dtb <> 0) then ibq2.ParamByName('czp').AsDateTime:=dtb-dta else ibq2.ParamByName('czp').Value:=null;
    ibq2.ParamByName('prac').AsInteger:=GetIdx(form15.ComboBox1.Text);
    ibq2.ParamByName('opis').AsString:=form15.Edit1.Text;
    ibq2.ParamByName('key_ob').AsInteger:=key_ob;
    try
      ibq2.Transaction.StartTransaction;
      ibq2.ExecSQL;
      ibq2.Transaction.Commit;
    except
      ibq2.Transaction.Rollback;
    end;
    FormShow(nil);
  end;

end;

// kasowanie
procedure TForm9.Button4Click(Sender: TObject);
var
  key_ob : integer;
begin
  key_ob:=ibq1.FieldByName('obecnosci_idx').AsInteger;
  if form5.ShowModal = mrOK then
  begin
    if ibt2.Active then ibt2.Rollback;
    ibq2.SQL.Clear;
    ibq2.SQL.Add('delete from obecnosci where obecnosci_idx = '+IntToStr(key_ob));
    try
      ibq2.Transaction.StartTransaction;
      ibq2.ExecSQL;
      ibq2.Transaction.Commit;
    except
      ibq2.Transaction.Rollback;
    end;
    FormShow(nil);
  end;
end;

end.

